/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package models.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.sql.Date;
import java.util.ArrayList;
import java.util.List;
import models.DBHandler;
import models.beans.MailBean;

/**
 *
 * @author Group 3 Aprotrain Aptech
 */
public class MailDAO {
    public static int sendMailForStudentRegisterCourses(String sender,String recipient,int coursesId) throws ClassNotFoundException, SQLException {
        int result = 0;
        ResultSet rs = null;
        Connection conn = DBHandler.getConnection();        
        PreparedStatement pstm = conn.prepareStatement("SELECT * FROM tblCourses WHERE coursesId=?");
        pstm.setInt(1, coursesId);
        rs = pstm.executeQuery();     
        if (rs.next()) {                        
            String coursesName = rs.getString("coursesName");
            String des = rs.getString("description");
            String attachment = rs.getString("attachment");           
            String content = "Hi,"+recipient+". course you have enrolled successfully:"+coursesName+"!";
            String subject = "Course registration";
            DateFormat dateFormat = new SimpleDateFormat("yyyy_MM_dd_HH_mm_ss");
            java.util.Date date = new java.util.Date();

            DateFormat dateFormatDate = new SimpleDateFormat("dd/MM/yyyy");
            String dateInsert = dateFormatDate.format(date);

            String aa =  dateFormat.format(date);
            pstm = conn.prepareStatement("INSERT INTO [Mail_Server].[dbo].[tblMail]([emailId],[sender],[recipient],[content],[subject],[attachment],[date],[status]) VALUES(?,?,?,?,?,?,?,?)");
            pstm.setString(1, aa+recipient);
            pstm.setString(2, sender);
            pstm.setString(3, recipient);
            pstm.setString(4, content);
            pstm.setString(5, subject);
            pstm.setString(6, attachment);
            pstm.setDate(7, getInsertDate(dateInsert));
            pstm.setString(8, "unread");
            result = pstm.executeUpdate();
        }                

        DBHandler.closeDBObj(pstm);
        DBHandler.closeDBObj(conn);
        return result;
    }
    public static List<MailBean> getMailByUserId(String userId) throws ClassNotFoundException, SQLException {
        List<MailBean> list = new ArrayList<MailBean>();
        ResultSet rs = null;
        Connection conn = DBHandler.getConnection();
        PreparedStatement pstm = conn.prepareStatement("SELECT * FROM tblMail WHERE recipient=? AND status != 'trash'");
        pstm.setString(1, userId);
        rs = pstm.executeQuery();

        MailBean mailBean = null;
        while (rs.next()) {
            mailBean = new MailBean();
            mailBean.setEmailId(rs.getString("emailId"));
            mailBean.setSender(rs.getString("sender"));
            String content = rs.getString("content");
            String valueContent = "";
            if (content == null || content.length() <= 0) {
            valueContent = "_";
            } else if (content.length() <= 50) {
                valueContent = content;
            } else {
                valueContent = content.substring(0, 50);
            }
            mailBean.setContent(valueContent);
            mailBean.setSubject(rs.getString("subject"));
            mailBean.setAttachment(rs.getString("attachment"));
            mailBean.setDate(convertDateToString(rs.getDate("date")));           
            mailBean.setStatus(rs.getString("status"));
            list.add(mailBean);
        }
        DBHandler.closeDBObj(rs);
        DBHandler.closeDBObj(pstm);
        DBHandler.closeDBObj(conn);

        return list;
    }
    public static List<MailBean> getMailOutBox(String userId) throws ClassNotFoundException, SQLException {
        List<MailBean> list = new ArrayList<MailBean>();
        ResultSet rs = null;
        Connection conn = DBHandler.getConnection();
        PreparedStatement pstm = conn.prepareStatement("SELECT * FROM tblMail WHERE sender=? AND status != 'trash'");
        pstm.setString(1, userId);
        rs = pstm.executeQuery();

        MailBean mailBean = null;
        while (rs.next()) {
            mailBean = new MailBean();
            mailBean.setEmailId(rs.getString("emailId"));
            mailBean.setSender(rs.getString("sender"));
            String content = rs.getString("content");
            String valueContent = "";
            if (content == null || content.length() <= 0) {
            valueContent = "_";
            } else if (content.length() <= 50) {
                valueContent = content;
            } else {
                valueContent = content.substring(0, 50);
            }
            mailBean.setContent(valueContent);
            mailBean.setSubject(rs.getString("subject"));
            mailBean.setAttachment(rs.getString("attachment"));
            mailBean.setDate(convertDateToString(rs.getDate("date")));
            mailBean.setStatus(rs.getString("status"));
            mailBean.setRecipient(rs.getString("recipient"));
            list.add(mailBean);
        }
        DBHandler.closeDBObj(rs);
        DBHandler.closeDBObj(pstm);
        DBHandler.closeDBObj(conn);

        return list;
    }
    public static List<MailBean> getMailTrash(String userId) throws ClassNotFoundException, SQLException {
        List<MailBean> list = new ArrayList<MailBean>();
        ResultSet rs = null;
        Connection conn = DBHandler.getConnection();
        PreparedStatement pstm = conn.prepareStatement("SELECT * FROM tblMail WHERE recipient=? AND status = 'trash'");
        pstm.setString(1, userId);
        rs = pstm.executeQuery();

        MailBean mailBean = null;
        while (rs.next()) {
            mailBean = new MailBean();
            mailBean.setEmailId(rs.getString("emailId"));
            mailBean.setSender(rs.getString("sender"));
            String content = rs.getString("content");
            String valueContent = "";
            if (content == null || content.length() <= 0) {
            valueContent = "_";
            } else if (content.length() <= 50) {
                valueContent = content;
            } else {
                valueContent = content.substring(0, 50);
            }
            mailBean.setContent(valueContent);
            mailBean.setSubject(rs.getString("subject"));
            mailBean.setAttachment(rs.getString("attachment"));
            mailBean.setDate(convertDateToString(rs.getDate("date")));
            mailBean.setStatus(rs.getString("status"));
            mailBean.setRecipient(rs.getString("recipient"));
            list.add(mailBean);
        }
        DBHandler.closeDBObj(rs);
        DBHandler.closeDBObj(pstm);
        DBHandler.closeDBObj(conn);

        return list;
    }
    public static MailBean getMailByEmailId(String emailId) throws ClassNotFoundException, SQLException {        
        ResultSet rs = null;
        Connection conn = DBHandler.getConnection();
        PreparedStatement pstm = conn.prepareStatement("SELECT * FROM tblMail WHERE emailId=?");
        pstm.setString(1, emailId);
        rs = pstm.executeQuery();

        MailBean mailBean = null;
        if (rs.next()) {
            mailBean = new MailBean();
            mailBean.setEmailId(rs.getString("emailId"));
            mailBean.setSender(rs.getString("sender"));
            String content = rs.getString("content");            
            mailBean.setContent(content);
            mailBean.setSubject(rs.getString("subject"));
            mailBean.setAttachment(rs.getString("attachment"));
            mailBean.setDate(convertDateToString(rs.getDate("date")));
            mailBean.setStatus(rs.getString("status"));
            mailBean.setRecipient(rs.getString("recipient"));
        }
        DBHandler.closeDBObj(rs);
        DBHandler.closeDBObj(pstm);
        DBHandler.closeDBObj(conn);

        return mailBean;
    }
    public static int sendMailForReply(MailBean mail) throws ClassNotFoundException, SQLException {
        int result = 0;
        ResultSet rs = null;
        Connection conn = DBHandler.getConnection();                     
        String subject = "Re:"+mail.getSubject();
        DateFormat dateFormat = new SimpleDateFormat("yyyy_MM_dd_HH_mm_ss");
        java.util.Date date = new java.util.Date();

        DateFormat dateFormatDate = new SimpleDateFormat("dd/MM/yyyy");
        String dateInsert = dateFormatDate.format(date);

        String aa =  dateFormat.format(date);
        PreparedStatement pstm = conn.prepareStatement("INSERT INTO [Mail_Server].[dbo].[tblMail]([emailId],[sender],[recipient],[content],[subject],[attachment],[date],[status]) VALUES(?,?,?,?,?,?,?,?)");
        pstm.setString(1, aa);
        pstm.setString(2, mail.getRecipient());
        pstm.setString(3, mail.getSender());
        pstm.setString(4, mail.getContent());
        pstm.setString(5, subject);
        pstm.setString(6, mail.getAttachment());
        pstm.setDate(7, getInsertDate(dateInsert));
        pstm.setString(8, "unread");
        result = pstm.executeUpdate();


        DBHandler.closeDBObj(pstm);
        DBHandler.closeDBObj(conn);
        return result;
    }
    public static int sendMail(MailBean mail) throws ClassNotFoundException, SQLException {
        int result = 0;
        ResultSet rs = null;
        Connection conn = DBHandler.getConnection();
        String subject = mail.getSubject();
        DateFormat dateFormat = new SimpleDateFormat("yyyy_MM_dd_HH_mm_ss");
        java.util.Date date = new java.util.Date();

        DateFormat dateFormatDate = new SimpleDateFormat("dd/MM/yyyy");
        String dateInsert = dateFormatDate.format(date);

        String aa =  dateFormat.format(date);
        String recipient = mail.getRecipient();
        String delimiter = ";";
        String[] vl = recipient.split(delimiter);
        for(int i =0; i < vl.length ; i++){
            PreparedStatement pstm = conn.prepareStatement("INSERT INTO [Mail_Server].[dbo].[tblMail]([emailId],[sender],[recipient],[content],[subject],[attachment],[date],[status]) VALUES(?,?,?,?,?,?,?,?)");
            pstm.setString(1, aa+vl[i]);
            pstm.setString(2, mail.getSender());
            pstm.setString(3, vl[i]);
            pstm.setString(4, mail.getContent());
            pstm.setString(5, subject);
            pstm.setString(6, mail.getAttachment());
            pstm.setDate(7, getInsertDate(dateInsert));
            pstm.setString(8, "unread");
            result = pstm.executeUpdate();
            DBHandler.closeDBObj(pstm);
            
        }
        DBHandler.closeDBObj(conn);
        return result;
    }
    public static int updateStatusReadMail(String emailId) throws ClassNotFoundException, SQLException {
        int result = 0;
        ResultSet rs = null;
        Connection conn = DBHandler.getConnection();
        
        PreparedStatement pstm = conn.prepareStatement("UPDATE [Mail_Server].[dbo].[tblMail] SET [status]='read' WHERE [emailId]=?");
        pstm.setString(1, emailId);
        result = pstm.executeUpdate();


        DBHandler.closeDBObj(pstm);
        DBHandler.closeDBObj(conn);
        return result;
    }
    public static int updateStatusTrashMail(String emailId) throws ClassNotFoundException, SQLException {
        int result = 0;
        ResultSet rs = null;
        Connection conn = DBHandler.getConnection();

        PreparedStatement pstm = conn.prepareStatement("UPDATE [Mail_Server].[dbo].[tblMail] SET [status]='trash' WHERE [emailId]=?");
        pstm.setString(1, emailId);
        result = pstm.executeUpdate();


        DBHandler.closeDBObj(pstm);
        DBHandler.closeDBObj(conn);
        return result;
    }
    public static int recoveryMail(String emailId) throws ClassNotFoundException, SQLException {
        int result = 0;
        ResultSet rs = null;
        Connection conn = DBHandler.getConnection();

        PreparedStatement pstm = conn.prepareStatement("UPDATE [Mail_Server].[dbo].[tblMail] SET [status]='read' WHERE [emailId]=?");
        pstm.setString(1, emailId);
        result = pstm.executeUpdate();


        DBHandler.closeDBObj(pstm);
        DBHandler.closeDBObj(conn);
        return result;
    }
    public static int deleteMail(String emailId) throws ClassNotFoundException, SQLException {
        int result = 0;
        ResultSet rs = null;
        Connection conn = DBHandler.getConnection();

        PreparedStatement pstm = conn.prepareStatement("DELETE FROM [Mail_Server].[dbo].[tblMail] WHERE [emailId]=?");
        pstm.setString(1, emailId);
        result = pstm.executeUpdate();


        DBHandler.closeDBObj(pstm);
        DBHandler.closeDBObj(conn);
        return result;
    }
    public static Date getInsertDate(String s){
        java.sql.Date dte=null;
        try{
        String str = s;
        SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
        java.util.Date dt = formatter.parse(str);
        dte=new java.sql.Date(dt.getTime());
        }catch(Exception e){
        e.printStackTrace();
        }
        return dte;
    }
    public static String convertDateToString(java.util.Date date) {
        SimpleDateFormat mySimpleDateFormat = new SimpleDateFormat("dd/MM/yyyy");
        return mySimpleDateFormat.format(date);
    }
    public static List<MailBean> getAllStudent() throws ClassNotFoundException, SQLException {
        List<MailBean> list = new ArrayList<MailBean>();
        ResultSet rs = null;
        Connection conn = DBHandler.getConnection();
        PreparedStatement pstm = conn.prepareStatement("SELECT * FROM tblUser WHERE isStaff = 0");
        rs = pstm.executeQuery();

        MailBean mailBean = null;
        while (rs.next()) {
            mailBean = new MailBean();
            mailBean.setRecipient(rs.getString("userId"));

            list.add(mailBean);
        }
        DBHandler.closeDBObj(rs);
        DBHandler.closeDBObj(pstm);
        DBHandler.closeDBObj(conn);

        return list;
    }
    public static List<MailBean> getAllEmailId() throws ClassNotFoundException, SQLException {
        List<MailBean> list = new ArrayList<MailBean>();
        ResultSet rs = null;
        Connection conn = DBHandler.getConnection();
        PreparedStatement pstm = conn.prepareStatement("SELECT * FROM tblUser");
        rs = pstm.executeQuery();

        MailBean mailBean = null;
        while (rs.next()) {
            mailBean = new MailBean();
            mailBean.setRecipient(rs.getString("userId"));

            list.add(mailBean);
        }
        DBHandler.closeDBObj(rs);
        DBHandler.closeDBObj(pstm);
        DBHandler.closeDBObj(conn);

        return list;
    }
}
